package com.bw.mapper;

import com.bw.domain.Sugar;
import com.bw.domain.Sugar1;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SugarMapper {
    @Select("SELECT p.`name`,sum(i.final_total_amount) value FROM base_province p,order_info i WHERE p.id=i.province_id GROUP BY p.`name`")
    List<Sugar> getSugarList1();

    @Select("SELECT t.name,sum(i.final_total_amount) value FROM order_info i,order_detail d,sku_info s,base_trademark t WHERE i.id=d.order_id and d.sku_id=s.id and s.tm_id=t.id GROUP BY t.name")
    List<Sugar> getSugarList2();

    @Select("SELECT c1.name,sum(i.final_total_amount) value from order_info i,order_detail d,sku_info s,base_category3 c3,base_category2 c2,base_category1 c1 WHERE i.id=d.order_id and d.sku_id=s.id and s.category3_id=c3.id and c3.category2_id=c2.id and c2.category1_id=c1.id GROUP BY c1.name ")
    List<Sugar> getSugarList3();

    @Select("SELECT a.num value,b.num value1 from \n" +
            "(SELECT count(*) num from user_info WHERE TIMESTAMPDIFF(month,create_time,NOW())<2) a,\n" +
            "(SELECT count(*) num from user_info WHERE TIMESTAMPDIFF(month,create_time,NOW())>=2) b")
    List<Sugar1> getSugarList4();
}
